﻿CREATE TABLE [CD].[Parties] (
    [Id]           INT             NOT NULL,
    [DepartmentId] AS              ([Id]/(100000000)),
    [DocId]        INT             CONSTRAINT [DF_Parties_DocId] DEFAULT ((0)) NOT NULL,
    [GoodsId]      INT             NOT NULL,
    [RetailPrice]  DECIMAL (19, 2) NOT NULL,
    [BarCode]      CHAR (12)       NOT NULL,
    [InputLineId]  INT             NOT NULL,
    [Blocked]      BIT             CONSTRAINT [DF_Parties_Bklocked] DEFAULT ((1)) NOT NULL,
    [RestQntty]    INT             CONSTRAINT [DF_Parties_RestQntty] DEFAULT ((0)) NOT NULL,
    [Note]         VARCHAR (250)   NULL,
    [MD]           DATETIME        CONSTRAINT [DF__Parties__MD__664B26CC] DEFAULT (getdate()) NOT NULL,
    [MW]           VARCHAR (50)    CONSTRAINT [DF__Parties__MW__673F4B05] DEFAULT (suser_sname()) NOT NULL,
    CONSTRAINT [PK_Parties] PRIMARY KEY NONCLUSTERED ([Id] ASC) WITH (FILLFACTOR = 80),
    CONSTRAINT [CK_Parties_RestQntty] CHECK ([RestQntty]>=(0)),
    CONSTRAINT [FK_Parties_Documents] FOREIGN KEY ([DocId]) REFERENCES [CA].[Documents] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION,
    CONSTRAINT [FK_Parties_Goods] FOREIGN KEY ([GoodsId]) REFERENCES [CA].[Goods] ([Id]) ON UPDATE CASCADE,
    CONSTRAINT [FK_Parties_InputLines] FOREIGN KEY ([InputLineId]) REFERENCES [CD].[InputLines] ([Id]) NOT FOR REPLICATION
);


GO
CREATE NONCLUSTERED INDEX [IX_Parties_DocId]
    ON [CD].[Parties]([DocId] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_Parties_InputLineId]
    ON [CD].[Parties]([InputLineId] ASC)
    INCLUDE([DocId]) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_Parties_BarCode]
    ON [CD].[Parties]([BarCode] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_Parties_GoodsId]
    ON [CD].[Parties]([GoodsId] ASC)
    INCLUDE([DocId]);


GO
CREATE NONCLUSTERED INDEX [IX_Parties_DepartmentId]
    ON [CD].[Parties]([DepartmentId] ASC)
    INCLUDE([RestQntty]);


GO
CREATE NONCLUSTERED INDEX [IX_Parties_MD]
    ON [CD].[Parties]([MD] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_Parties_GoodsId_Id]
    ON [CD].[Parties]([GoodsId] ASC, [Id] ASC)
    INCLUDE([DocId], [MD]);


GO
CREATE NONCLUSTERED INDEX [IX_Parties_RestQntty]
    ON [CD].[Parties]([RestQntty] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_Parties_Blocked]
    ON [CD].[Parties]([Blocked] ASC)
    INCLUDE([Id], [GoodsId]);


GO

CREATE TRIGGER CD.PartiesDeleteLog ON CD.Parties AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;

INSERT INTO [PR].[DocDeleteLog] (TableName, IdValue, MD, MW)
SELECT 'CD.Parties', Id, getdate(), SYSTEM_USER FROM DELETED

END

GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Остатки по партии д.б. >= 0', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'Parties', @level2type = N'CONSTRAINT', @level2name = N'CK_Parties_RestQntty';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Идентификатор документа, создавшего строку', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'Parties', @level2type = N'COLUMN', @level2name = N'DocId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'1-продажа товара из партии запрещена', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'Parties', @level2type = N'COLUMN', @level2name = N'Blocked';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Текущий остаток по партии', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'Parties', @level2type = N'COLUMN', @level2name = N'RestQntty';

